package DBLayer;
import java.sql.*;
import java.util.ArrayList;
import ModelLayer.Product;
import ModelLayer.SalesOrder;


public class DBProduct implements IFDBProduct {
     private  Connection con;
    public DBProduct() {
       con = DBConnection.getInstance().getDBcon();
    }
   
    public Product findProduct(int productID, boolean retriveAssociation)
    {
        Product protObj = new Product();
        protObj = singleWhere("productID = " + productID, retriveAssociation);
        return protObj;
    }
    private Product singleWhere(String wClause, boolean retrieveAssociation)
    {	
        ResultSet results;
        Product proObj = new Product();
        String query =  buildQuery(wClause);
        System.out.println("DBProduct -singelWhere " + query); 
        try{
	   Statement stmt = con.createStatement();
	   stmt.setQueryTimeout(5);
	   results = stmt.executeQuery(query);
	   int snr=0;
	   if( results.next() ){
	       proObj = buildProduct(results);
               
           }
	   stmt.close();
	}
	catch(Exception e){
	   System.out.println("Query exception - select department : "+e);
	   
        }
	return proObj;

    
    }
    private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Product> list = new ArrayList<Product>();
		
		String query =  buildQuery(wClause);
                System.out.println("DbDepartment " + query); 
  		try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
			int snr=0;
			while( results.next() ){
		     	 Product proObj = new Product();
			 proObj = buildProduct(results);	
                         list.add(proObj);
                         
			}
			stmt.close();
		}	
	 	catch(Exception e){
	 		System.out.println("Query exception - select product : "+e);
	 	}
		return list;
	}
    private Product buildProduct(ResultSet results)
    {
       Product proObj = new Product();
         try{
               proObj.setProductID(results.getInt(1));
               proObj.setName(results.getString(2));
               proObj.setPurchasePrice(results.getDouble(3));
               proObj.setSalesPrice(results.getDouble(4));
               proObj.setRentPrice(results.getDouble(5));
               proObj.setCountryOfOrigin(results.getString(6));
               proObj.setMinStock(results.getInt(7));
               proObj.setSize(results.getDouble(8));
               proObj.setColour(results.getString(9));
               proObj.setType(results.getString(10));
               proObj.setDescription(results.getString(11));
               proObj.setFabric(results.getString(12));
               proObj.setCalibre(results.getDouble(13));
               
               System.out.println("build Product" + proObj.getName());
          }
         catch(Exception e)
         {
             System.out.println("building product object");
         }
        
        return proObj;
        
    }
 
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM product";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}

    public ArrayList<Product> getAllProducts(boolean retriveAssociation) {
        ArrayList<Product> list = new ArrayList();
        return list;
    }

   

    public Product searchProductOnName(String name, boolean retriveAssociation) {
       return null;
    }

    public int insertProduct(Product pro)
    {  
        int nextID = GetMax.getMaxId("Select max(productID) from Product");
        nextID = nextID + 1;
        System.out.println("next ID = " +  nextID);
  
       int rc = -1;
	   String query="INSERT INTO Product(productID, name, purchasePrice, salePrice, rentPrice, countryOfOrigin, minStock, size, colour, type, description, fabric, calibre, supplierID)  VALUES('"+
                   pro.getProductID()  + "','"  +
                   pro.getName()  + "','"  +
                   pro.getPurchasePrice()  + "','"  +
                   pro.getSalesPrice()  + "','"  +
                   pro.getRentPrice()  + "','"  +
                   pro.getCountryOfOrigin()  + "','"  +
                   pro.getMinStock()  + "','"  +
                   pro.getSize()  + "','"  +
                   pro.getColour()  + "','"  +
                   pro.getType()  + "','"  +
                   pro.getDescription()  + "','"  +
                   pro.getFabric()  + "','"  +
                   pro.getCalibre()  + "','"  +
                   pro.getSupplierID()  + "','"  +
                        
                       "')";
                     
  
	   return(rc);
	}

	public int updateProduct (Product pro)
	{
		Product proObj  = pro;
		int rc=-1;

		String query="UPDATE Customer SET "+
		 	"name ='"+ proObj.getName()+"', "+
                        "address ='"+ proObj.getPurchasePrice() + "', " +
                        "zipCode ='"+ proObj.getSalesPrice() + "' " +
                        "city ='"+ proObj.getRentPrice() + "' " +
                        "phoneNo ='"+ proObj.getCountryOfOrigin() + "' " +
                        "phoneNo ='"+ proObj.getMinStock() + "' " +
                        "phoneNo ='"+ proObj.getSize() + "' " +
                        "phoneNo ='"+ proObj.getColour() + "' " +
                        "phoneNo ='"+ proObj.getType() + "' " +
                        "phoneNo ='"+ proObj.getDescription() + "' " +
                        "phoneNo ='"+ proObj.getFabric() + "' " +
                        "phoneNo ='"+ proObj.getCalibre() + "' " +
                        "phoneNo ='"+ proObj.getSupplierID() + "' " +
		        " WHERE productID = '"+ proObj.getProductID() + "'";
                System.out.println("Update query:" + query);
  		try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in product db: "+ex);
	  	}
		return(rc);
	}
	
	public int deleteProduct(int productID)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM Product WHERE productID = '" +
				productID + "'";
                System.out.println(query);
	  	try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in product db: "+ex);
   	        }
		return(rc);
	}
	
} 